-- 关单任务管理平台数据库初始化脚本
-- 数据库：SQL Server 2008

-- 创建数据库（如果不存在）
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'TaskFlowDB')
BEGIN
    CREATE DATABASE TaskFlowDB;
END
GO

USE TaskFlowDB;
GO

-- 1. 关单数据表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[V_EntryData]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[V_EntryData] (
        [PRE_ENTRY_ID] varchar(500) NULL ,
        [ENTRY_ID] varchar(500) NOT NULL PRIMARY KEY ,
        [SEQ_NO] varchar(500) NULL ,
        [CUSTOM_MASTER] varchar(500) NULL ,
        [CUSTOM_MASTER_NAME] varchar(500) NULL ,
        [TRADE_CO] varchar(500) NULL ,
        [TRADE_NAME] varchar(500) NULL ,
        [TRADE_CO_SCC] varchar(500) NULL ,
        [I_E_PORT] varchar(500) NULL ,
        [I_E_PORT_NAME] varchar(500) NULL ,
        [I_E_DATE] varchar(500) NULL ,
        [D_DATE] varchar(500) NULL ,
        [MANUAL_NO] varchar(500) NULL ,
        [OVERSEAS_CONSIGNEE_CODE] varchar(500) NULL ,
        [OVERSEAS_CONSIGNEE_ENAME] varchar(500) NULL ,
        [TRAF_MODE] varchar(500) NULL ,
        [TRAF_MODE_NAME] varchar(500) NULL ,
        [TRAF_NAME] varchar(500) NULL ,
        [VOYAGE_NO] varchar(500) NULL ,
        [BILL_NO] varchar(500) NULL ,
        [OWNER_CODE] varchar(500) NULL ,
        [OWNER_NAME] varchar(500) NULL ,
        [OWNER_CODE_SCC] varchar(500) NULL ,
        [TRADE_MODE] varchar(500) NULL ,
        [TRADE_MODE_NAME] varchar(500) NULL ,
        [CUT_MODE] varchar(500) NULL ,
        [CUT_NAME] varchar(500) NULL ,
        [LICENSE_NO] varchar(500) NULL ,
        [CONTR_NO] varchar(500) NULL ,
        [TRADE_AREA_CODE] varchar(500) NULL ,
        [TRADE_AREA_NAME] varchar(500) NULL ,
        [TRADE_COUNTRY] varchar(500) NULL ,
        [TRADE_COUNTRY_NAME] varchar(500) NULL ,
        [DISTINATE_PORT] varchar(500) NULL ,
        [DISTINATE_PORT_NAME] varchar(500) NULL ,
        [ENTRY_PORT_CODE] varchar(500) NULL ,
        [ENTRY_PORT_NAME] varchar(500) NULL ,
        [WRAP_TYPE] varchar(500) NULL ,
        [WRAP_TYPE_NAME] varchar(500) NULL ,
        [PACK_NO] varchar(500) NULL ,
        [NET_WT] varchar(500) NULL ,
        [GROSS_WT] varchar(500) NULL ,
        [TRANS_MODE] varchar(500) NULL ,
        [TRANS_MODE_NAME] varchar(500) NULL ,
        [FEE_RATE] varchar(500) NULL ,
        [INSUR_RATE] varchar(500) NULL ,
        [OTHER_RATE] varchar(500) NULL ,
        [CERT_MARK] varchar(500) NULL ,
        [NOTE_S] varchar(500) NULL ,
        [G_NO] varchar(500) NULL ,
        [CODE_TS] varchar(500) NULL ,
        [G_NAME] varchar(500) NULL ,
        [G_MODEL] varchar(500) NULL ,
        [QTY] varchar(500) NULL ,
        [QTY_UNIT] varchar(500) NULL ,
        [QTY_1] varchar(500) NULL ,
        [QTY_1_UNIT] varchar(500) NULL ,
        [QTY_2] varchar(500) NULL ,
        [QTY_2_UNIT] varchar(500) NULL ,
        [DECL_PRICE] varchar(500) NULL ,
        [DECL_TOTAL] varchar(500) NULL ,
        [TRADE_CURR] varchar(500) NULL ,
        [TRADE_CURR_NAME] varchar(500) NULL ,
        [ORIGIN_COUNTRY] varchar(500) NULL ,
        [ORIGIN_COUNTRY_NAME] varchar(500) NULL ,
        [DESTINATION_COUNTRY] varchar(500) NULL ,
        [DESTINATION_COUNTRY_NAME] varchar(500) NULL ,
        [DISTRICT_CODE] varchar(500) NULL ,
        [DISTRICT_NAME] varchar(500) NULL ,
        [DUTY_MODE] varchar(500) NULL ,
        [DUTY_MODE_NAME] varchar(500) NULL ,
        [AGENT_CODE] varchar(500) NULL ,
        [AGENT_NAME] varchar(500) NULL ,
        [AGENT_CODE_SCC] varchar(500) NULL ,
        [PROMISE_MARK_1] varchar(500) NULL ,
        [PROMISE_MARK_2] varchar(500) NULL ,
        [PROMISE_MARK_3] varchar(500) NULL
    );
END
GO

-- 2. 下载任务表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DownloadTask]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[DownloadTask] (
        [id] bigint IDENTITY(1,1) PRIMARY KEY,
        [task_name] varchar(200) NOT NULL,
        [company_name] varchar(200) NULL,
        [company_tax_no] varchar(100) NULL,
        [entry_id] varchar(100) NULL,
        [export_date_start] varchar(20) NULL,
        [export_date_end] varchar(20) NULL,
        [declare_date_start] varchar(20) NULL,
        [declare_date_end] varchar(20) NULL,
        [cron_expression] varchar(100) NOT NULL,
        [status] int DEFAULT 1,  -- 0:禁用, 1:启用, 2:运行中
        [create_time] datetime DEFAULT GETDATE(),
        [update_time] datetime DEFAULT GETDATE(),
        [last_execute_time] datetime NULL,
        [remark] varchar(500) NULL
    );
END
GO

-- 3. 下载日志表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DownloadLog]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[DownloadLog] (
        [id] bigint IDENTITY(1,1) PRIMARY KEY,
        [task_id] bigint NOT NULL,
        [task_name] varchar(200) NOT NULL,
        [start_time] datetime NOT NULL,
        [end_time] datetime NULL,
        [execute_status] int DEFAULT 0,  -- 0:执行中, 1:成功, 2:失败
        [total_count] int DEFAULT 0,
        [new_count] int DEFAULT 0,
        [duplicate_count] int DEFAULT 0,
        [error_msg] varchar(2000) NULL,
        [create_time] datetime DEFAULT GETDATE()
    );
END
GO

-- 创建索引
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_task_status' AND object_id = OBJECT_ID('DownloadTask'))
BEGIN
    CREATE INDEX idx_task_status ON DownloadTask(status);
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_log_task_id' AND object_id = OBJECT_ID('DownloadLog'))
BEGIN
    CREATE INDEX idx_log_task_id ON DownloadLog(task_id);
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_log_create_time' AND object_id = OBJECT_ID('DownloadLog'))
BEGIN
    CREATE INDEX idx_log_create_time ON DownloadLog(create_time);
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_entry_trade_name' AND object_id = OBJECT_ID('V_EntryData'))
BEGIN
    CREATE INDEX idx_entry_trade_name ON V_EntryData(TRADE_NAME);
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_entry_ie_date' AND object_id = OBJECT_ID('V_EntryData'))
BEGIN
    CREATE INDEX idx_entry_ie_date ON V_EntryData(I_E_DATE);
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'idx_entry_d_date' AND object_id = OBJECT_ID('V_EntryData'))
BEGIN
    CREATE INDEX idx_entry_d_date ON V_EntryData(D_DATE);
END
GO

PRINT '数据库初始化完成！';

